package com.hbnu.mysql.Flower;

import com.hbnu.mysql.User.LRService;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
 * @author zouting
 * @Date 2025-11-05-22:19
 *
 */public class FlowerDAO {
    public List<Flower> listAll() throws Exception {
        // 鲜花表查询
        String sql = "SELECT fid, fname, language, price, stock, category, image_url FROM tb_flower";
        List<Flower> list = new ArrayList<>();
        try (Connection conn = LRService.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(new Flower(
                        rs.getString("fid"),
                        rs.getString("fname"),
                        rs.getString("language"),
                        rs.getBigDecimal("price"),
                        rs.getInt("stock"),
                        rs.getString("category"),
                        rs.getString("image_url")
                ));
            }
        }
        return list;
    }

    /*
    * 按分类查询鲜花
    * */
    public List<Flower> listByCategory(String category) throws Exception {
        String sql = "SELECT fid, fname, language, price, stock, category, image_url FROM tb_flower WHERE category = ?";
        List<Flower> list = new ArrayList<>();
        try (Connection conn = LRService.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setString(1, category);
            try (ResultSet rs = preparedStatement.executeQuery()) {
                while (rs.next()) {
                    list.add(new Flower(
                            rs.getString("fid"),
                            rs.getString("fname"),
                            rs.getString("language"),
                            rs.getBigDecimal("price"),
                            rs.getInt("stock"),
                            rs.getString("category"),
                            rs.getString("image_url")
                    ));
                }
            }
        }
             return list;
    }

    /*
    * 按ID查询鲜花
    * */
    public Flower getFlowersById(String fid) throws Exception {
        String sql = "SELECT fid, fname, language, price, stock, category, image_url FROM tb_flower WHERE fid = ?";
        try (Connection conn = LRService.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setString(1, fid);
            try (ResultSet rs = preparedStatement.executeQuery()) {
                if (rs.next()) {
                    return new Flower(
                            rs.getString("fid"),
                            rs.getString("fname"),
                            rs.getString("language"),
                            rs.getBigDecimal("price"),
                            rs.getInt("stock"),
                            rs.getString("category"),
                            rs.getString("image_url")
                    );
                }
            }
        }
        return null;
    }

    /*
    * 检查库存是否足够
    * */
    public boolean checkStock(String fid, int quantity) throws Exception {
        String sql = "SELECT stock FROM tb_flower WHERE fid = ?";
        try (Connection conn = LRService.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setString(1, fid);
            try (ResultSet rs = preparedStatement.executeQuery()) {
                if (rs.next()) {
                    int stock = rs.getInt("stock");
                    return stock >= quantity;
                }
            }
        }
        return false;
    }

    /*
    * 更新库存
    * */
    public void updateStock(String fid, int quantity) throws Exception {
        String sql = "UPDATE tb_flower SET stock = stock - ? WHERE fid = ?";
        try (Connection conn = LRService.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setInt(1, quantity);
            preparedStatement.setString(2, fid);
            preparedStatement.executeUpdate();
        }
    }


    /* 加入购物车：若已存在则数量+1 */
    public void addToCart(String username, String fid) throws Exception {
        //先检查库存
        if (!checkStock(fid, 1)) {
            throw new Exception("库存不足，无法添加到购物车！");
        }
        String sql = "INSERT INTO tb_cart(username,fid,quantity) VALUES(?,?,1) " +
                "ON DUPLICATE KEY UPDATE quantity=quantity+1";
        try (Connection conn = LRService.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, username);
            ps.setString(2, fid);
            ps.executeUpdate();
        } catch (Exception e) {  // 捕获Exception并转换为SQLException
            e.printStackTrace();
        }
    }

    /*
    * 获取所有的分类
    * */
    public List<String> getAllCategories() throws Exception {
        String sql = "SELECT DISTINCT category FROM tb_flower where category is not null";
        List<String> categories = new ArrayList<>();
        try (Connection conn = LRService.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                categories.add(rs.getString("category"));
            }
        }
        return categories;
    }
}


